/*/{Protheus.doc} SYNCPAR
E a User Function de geracao do layout PAR (Contas a Pagar e Contas a Receber) do Sistema Synchro
@author Elton Teodoro Alves
@since 14/11/2014
@version P11 R5
@Param aParam,Array,Array enviado pelo schudele
@Example
Esta User Function foi desenvolvida para ser utilizada com o Schudele, que passa como parametro para User Function um array com a seguinte configuracao:</br>
</br>
<table>
<tr><th> POSICAO </th><th> TIPO </th><th> DESCRICAO             </th></tr>
<tr><td> [01]    </td><td> C    </td><td> Codigo da Empresa     </td></tr>
<tr><td> [02]    </td><td> C    </td><td> Codigo da Filial      </td></tr>
<tr><td> [03]    </td><td> C    </td><td> Codigo do Agendamento </td></tr>
<tr><td> [04]    </td><td> C    </td><td> Codigo da Tarefa      </td></tr>
</table>
</br>	
Para teste fora do schudele, pode-se enviar para o SmartClient com a clausula -a estes dados separadas por virgula ',' Ex.: 99,01,0001,0001
/*/

#Define CRLF Chr(13) + Chr(10)
Static PERIOD := 'D'

#Include 'Protheus.ch'

User Function SYNCPAR(xParam)
	
	Local	cLayout		:=	Right(ProcName(),3)
	Local	aParam		:=	If(Valtype(xParam) == 'C',Separa(xParam,',',.T.),xParam)
	Local	aSyncEnv	:=	U_SYNCENV(aParam,cLayout,PERIOD)
	Local	cTrab		:=	GetNextAlias()
	Local	cTempTrab	:=	cLayout + Dtos(Date()) + StrTran(Time(),':','')
	Local	cTempFile	:=	GetSrvProfString("StartPath","") + cTempTrab + GetDbExtension()
	Local	cQuery		:=	''
	Local	cQuerySE1	:=	''
	Local	cQuerySE2	:=	''
	Local	cQueryBkp	:=	''
	Local	aStru		:=	{}
	Local	nX			:=	0
	Local	aFiliais	:=	StrTokArr(AllTrim(aSyncEnv[10]),'/',)
	Local	cFiliais	:=	''
	Local	nCount		:=	0
	Local	aNumParc	:=	{}
	
	// Monta a string com a lista de filiais para ser utilizada na query
	
	For nX := 1 To Len(aFiliais)
		
		cFiliais += "'" + aFiliais[nX] + "'"
		
		If nX # Len(aFiliais)
			
			cFiliais += ","
			
		End If
		
	Next nX
	
	// Caso as parcelas estejam definidas como letras este Array sera usado para fazer o depara para numero da parcela
	aAdd(aNumParc,'A')
	aAdd(aNumParc,'B')
	aAdd(aNumParc,'C')
	aAdd(aNumParc,'D')
	aAdd(aNumParc,'E')
	aAdd(aNumParc,'F')
	aAdd(aNumParc,'G')
	aAdd(aNumParc,'H')
	aAdd(aNumParc,'I')
	aAdd(aNumParc,'J')
	aAdd(aNumParc,'K')
	aAdd(aNumParc,'L')
	aAdd(aNumParc,'M')
	aAdd(aNumParc,'N')
	aAdd(aNumParc,'O')
	aAdd(aNumParc,'P')
	aAdd(aNumParc,'Q')
	aAdd(aNumParc,'R')
	aAdd(aNumParc,'S')
	aAdd(aNumParc,'T')
	aAdd(aNumParc,'U')
	aAdd(aNumParc,'V')
	aAdd(aNumParc,'W')
	aAdd(aNumParc,'X')
	aAdd(aNumParc,'Y')
	aAdd(aNumParc,'Z')
	
	// --------------------------------------------------------------
	// Monta estrutura do arquivo temporario com dados a serem exportados
	// --------------------------------------------------------------
	aAdd(aStru,{'POS0001','C',0001,0}) // CTRL_INSTRUCAO
	aAdd(aStru,{'POS0002','C',0040,0}) // DOF_IMPORT_NUMERO
	aAdd(aStru,{'POS0042','C',0008,0}) // DT_VENCIMENTO_PARCELA
	aAdd(aStru,{'POS0050','C',0018,0}) // INFORMANTE_EST_CODIGO
	aAdd(aStru,{'POS0068','C',0003,0}) // NUM_PARCELA
	aAdd(aStru,{'POS0071','C',0021,0}) // VL_PARCELA
	aAdd(aStru,{'POS0092','C',0021,0}) // VL_DESCONTO_CONDICIONAL
	aAdd(aStru,{'POS0113','C',0007,0}) // PERC_DESC_CONDICIONAL
	aAdd(aStru,{'POS0120','C',0021,0}) // VL_BASE_PIS_COFINS_CSLL_RET
	aAdd(aStru,{'POS0141','C',0021,0}) // VL_BASE_PIS_RET
	aAdd(aStru,{'POS0162','C',0021,0}) // VL_BASE_COFINS_RET
	aAdd(aStru,{'POS0183','C',0021,0}) // VL_BASE_CSLL_RET
	aAdd(aStru,{'POS0204','C',0021,0}) // VL_BASE_IRRF_RET
	aAdd(aStru,{'POS0225','C',0021,0}) // VL_BASE_ISS_RET
	aAdd(aStru,{'POS0246','C',0021,0}) // VL_PIS_COFINS_CSLL_RET
	aAdd(aStru,{'POS0267','C',0021,0}) // VL_PIS_RET
	aAdd(aStru,{'POS0288','C',0021,0}) // VL_COFINS_RET
	aAdd(aStru,{'POS0309','C',0021,0}) // VL_CSLL_RET
	aAdd(aStru,{'POS0330','C',0021,0}) // VL_IRRF_RET
	aAdd(aStru,{'POS0351','C',0021,0}) // VL_ISS_RET
	aAdd(aStru,{'POS0372','C',0021,0}) // VL_BASE_REDUZIDA_IRRF
	
	// --------------------------------------------------------------
	
	If aSyncEnv[6]
		
		// --------------------------------------------------------------
		// Montagem da query de pesquisa
		// --------------------------------------------------------------
		
		cQuerySE1	+=	"SELECT " + CRLF
		cQuerySE2	+=	"SELECT " + CRLF
		
		cQuerySE1	+=	"'SE1' ORIGEM," + CRLF
		cQuerySE2	+=	"'SE2' ORIGEM," + CRLF
		
		cQuerySE1	+=	"SF2.R_E_C_N_O_ RECNO," + CRLF
		cQuerySE2	+=	"SF1.R_E_C_N_O_ RECNO," + CRLF
		
		cQuerySE1	+=	"SF2.F2_DOC DOC," + CRLF
		cQuerySE2	+=	"SF1.F1_DOC DOC," + CRLF
		
		cQuerySE1	+=	"SF2.F2_SERIE SERIE," + CRLF
		cQuerySE2	+=	"SF1.F1_SERIE SERIE," + CRLF
		
		cQuerySE1	+=	"SF2.F2_EMISSAO EMISSAO," + CRLF
		cQuerySE2	+=	"SF1.F1_EMISSAO EMISSAO," + CRLF
		
		//---------------------------------------------
		
		cQuerySE1	+=	"'I' POS0001," + CRLF
		cQuerySE2	+=	"'I' POS0001," + CRLF
		
		cQuerySE1	+=	"'' POS0002," + CRLF
		cQuerySE2	+=	"'' POS0002," + CRLF
		
		cQuerySE1	+=	"SE1.E1_VENCTO POS0042," + CRLF
		cQuerySE2	+=	"SE2.E2_VENCTO POS0042," + CRLF
		
		cQuerySE1	+=	"SE1.E1_FILIAL POS0050," + CRLF
		cQuerySE2	+=	"SE2.E2_FILIAL POS0050," + CRLF
		
		cQuerySE1	+=	"SE1.E1_PARCELA POS0068," + CRLF
		cQuerySE2	+=	"SE2.E2_PARCELA POS0068," + CRLF
		
		cQuerySE1	+=	"SE1.E1_VALOR POS0071," + CRLF
		cQuerySE2	+=	"SE2.E2_VALOR POS0071," + CRLF
		
		cQuerySE1	+=	"SE1.E1_DECRESC POS0092," + CRLF
		cQuerySE2	+=	"SE2.E2_DECRESC POS0092," + CRLF
		
		cQuerySE1	+=	"0 POS0113," + CRLF
		cQuerySE2	+=	"0 POS0113," + CRLF
		
		cQuerySE1	+=	"SE1.E1_BASEPIS POS0120," + CRLF
		cQuerySE2	+=	"SE2.E2_BASEPIS POS0120," + CRLF
		
		cQuerySE1	+=	"SE1.E1_BASEPIS POS0141," + CRLF
		cQuerySE2	+=	"SE2.E2_BASEPIS POS0141," + CRLF
		
		cQuerySE1	+=	"SE1.E1_BASECOF POS0162," + CRLF
		cQuerySE2	+=	"SE2.E2_BASECOF POS0162," + CRLF
		
		cQuerySE1	+=	"SE1.E1_BASECSL POS0183," + CRLF
		cQuerySE2	+=	"SE2.E2_BASECSL POS0183," + CRLF
		
		cQuerySE1	+=	"SE1.E1_BASEIRF POS0204," + CRLF
		cQuerySE2	+=	"SE2.E2_BASEIRF POS0204," + CRLF
		
		cQuerySE1	+=	"SE1.E1_BASEISS POS0225," + CRLF
		cQuerySE2	+=	"SE2.E2_BASEISS POS0225," + CRLF
		
		cQuerySE1	+=	"SE1.E1_PIS + SE1.E1_COFINS + SE1.E1_CSLL POS0246," + CRLF
		cQuerySE2	+=	"SE2.E2_PIS + SE2.E2_COFINS + SE2.E2_CSLL POS0246," + CRLF
		
		cQuerySE1	+=	"SE1.E1_PIS POS0267," + CRLF
		cQuerySE2	+=	"SE2.E2_PIS POS0267," + CRLF
		
		cQuerySE1	+=	"SE1.E1_COFINS POS0288," + CRLF
		cQuerySE2	+=	"SE2.E2_COFINS POS0288," + CRLF
		
		cQuerySE1	+=	"SE1.E1_CSLL POS0309," + CRLF
		cQuerySE2	+=	"SE2.E2_CSLL POS0309," + CRLF
		
		cQuerySE1	+=	"SE1.E1_IRRF POS0330," + CRLF
		cQuerySE2	+=	"SE2.E2_IRRF POS0330," + CRLF
		
		cQuerySE1	+=	"SE1.E1_ISS POS0351," + CRLF
		cQuerySE2	+=	"SE2.E2_ISS POS0351," + CRLF
		
		cQuerySE1	+=	"0 POS0372" + CRLF
		cQuerySE2	+=	"0 POS0372" + CRLF
		
		// --------- FROM SE1 SE2
		
		cQuerySE1 +=	"FROM " + RetSqlName("SE1") + " SE1 " + CRLF
		cQuerySE2 +=	"FROM " + RetSqlName("SE2") + " SE2 " + CRLF
		
		// --------- LEFT JOIN SE1
		
		cQuerySE1 +=	"LEFT JOIN " + RetSqlName("SF2") + " SF2 ON " + CRLF
		cQuerySE1 +=	"SE1.E1_FILIAL = SF2.F2_FILIAL AND " + CRLF
		cQuerySE1 +=	"SE1.E1_PREFIXO	= SF2.F2_SERIE AND " + CRLF
		cQuerySE1 +=	"SE1.E1_NUM = SF2.F2_DOC AND " + CRLF
		cQuerySE1 +=	"SE1.E1_EMISSAO = SF2.F2_EMISSAO AND " + CRLF
		cQuerySE1 +=	"SE1.E1_CLIENTE = SF2.F2_CLIENTE AND " + CRLF
		cQuerySE1 +=	"SE1.E1_LOJA = SF2.F2_LOJA " + CRLF
		
		// --------- WHERE SE1
		
		cQuerySE1	+=	"WHERE SF2.F2_DAUTNFE = '" + aSyncEnv[9] + "' AND " + CRLF
		cQuerySE1	+=	"SF2.F2_FILIAL IN(" + cFiliais + ") AND ISNULL(SE1.D_E_L_E_T_,'') <> '*' AND " + CRLF
		cQuerySE1	+=	"SF2.F2_CHVNFE <> '" + Space(GetSx3Cache('F2_CHVNFE','X3_TAMANHO')) + "' AND " + CRLF
		cQuerySE1	+=	"ISNULL(SF2.D_E_L_E_T_,'') <> '*' " + CRLF
		
		// --------- LEFT JOIN SE2
		
		cQuerySE2 +=	"LEFT JOIN " + RetSqlName("SF1") + " SF1 ON " + CRLF
		cQuerySE2 +=	"SE2.E2_FILIAL = SF1.F1_FILIAL AND " + CRLF
		cQuerySE2 +=	"SE2.E2_PREFIXO	= SF1.F1_SERIE AND " + CRLF
		cQuerySE2 +=	"SE2.E2_NUM = SF1.F1_DOC AND " + CRLF
		cQuerySE2 +=	"SE2.E2_EMISSAO = SF1.F1_EMISSAO AND " + CRLF
		cQuerySE2 +=	"SE2.E2_FORNECE = SF1.F1_FORNECE AND " + CRLF
		cQuerySE2 +=	"SE2.E2_LOJA = SF1.F1_LOJA " + CRLF
		
		// --------- WHERE SE2
		
		cQuerySE2	+=	"WHERE (SF1.F1_DTDIGIT = '" + aSyncEnv[9] + "' OR SF1.F1_DAUTNFE = '" + aSyncEnv[9] + "') AND" + CRLF
		cQuerySE2	+=	"SE2.E2_FILIAL IN(" + cFiliais + ") AND ISNULL(SE2.D_E_L_E_T_,'') <> '*' AND " + CRLF
		cQuerySE2 +=	"ISNULL(SF1.D_E_L_E_T_,'') <> '*' " + CRLF
		
		// --------- UNION ALL
		
		cQuery	+=	cQuerySE1
		cQuery	+=	"UNION ALL " + CRLF
		cQuery	+=	cQuerySE2
		
		cQueryBkp	:=	cQuery
		
		cQuery := ChangeQuery(cQuery)
		
		If TcSqlExec(cQuery) < 0
			
			U_SYNCMAIL(AllTrim(aSyncEnv[15]),cLayout + ' - ' + AllTrim(aSyncEnv[17]),'Erro na execucao da query no banco de dados: ' + Chr(13) + Chr(10) + AllTrim(TcSqlError()),AllTrim(SubStr(aSyncEnv[4],1,6)),AllTrim(SubStr(aSyncEnv[4],7,6)),1,aSyncEnv[8],aSyncEnv[9])
			
		Else
			
			DbUseArea(.T.,"TOPCONN",TCGenQry(,,cQuery),cTrab,.F.,.T.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				cLayout + ' - ' + AllTrim(aSyncEnv[17]) + CRLF +;
				'Query executada com sucesso: ' + CRLF + cQueryBkp)
			
			// Cria arquivo temporario
			
			fErase(Separa(cTempFile,'\',.T.)[3])
			DbCreate(cTempFile,aStru,__LocalDriver)
			DbUseArea(.T.,__LocalDriver,cTempFile,cTempTrab,.T.,.F.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				'Arquivo Temporario criado com sucesso : ' + cTempFile)
			
			// --------------------------------------------------------------
			// Preenchimento do arquivo temporario
			// --------------------------------------------------------------
			
			(cTrab)->(DbGotop())
			
			Do While .Not. (cTrab)->(EOF())
				
				RecLock(cTempTrab,.T.)
				
				(cTempTrab)->POS0001 := (cTrab)->POS0001
				(cTempTrab)->POS0002 := U_SYNCFIL(aSyncEnv[7],5,(cTrab)->POS0002) + Str((cTrab)->RECNO,10) + (cTrab)->DOC + (cTrab)->SERIE + (cTrab)->EMISSAO
				(cTempTrab)->POS0042 := (cTrab)->POS0042
				(cTempTrab)->POS0050 := U_SYNCFIL(aSyncEnv[7],2,(cTrab)->POS0050) + U_SYNCFIL(aSyncEnv[7],3,(cTrab)->POS0050)
				
				// -------- Numero da Parcela
				// -------- Caso seja definido letras para a parcela faz a mudanca
				If Empty((cTrab)->POS0068)
					
					(cTempTrab)->POS0068 := '001'
					
				ElseIf Upper(AllTrim((cTrab)->POS0068)) $ 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
					
					(cTempTrab)->POS0068 := StrTran(Str(aScan(aNumParc,{|x| x[1] == (cTrab)->POS0068}),3),' ','0')
					
				ElseIf (cTrab)->POS0068 $ '0123456789'
					
					(cTempTrab)->POS0068 := StrTran(Str((cTrab)->POS0068,3),' ','0')
					
				End IF
				//--------------------------
				
				
				(cTempTrab)->POS0071 := '+' + StrTran(Transform((cTrab)->POS0071,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0092 := '+' + StrTran(Transform((cTrab)->POS0092,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0113 := '+' + StrTran(Transform((cTrab)->POS0113,'@E '+ Replicate('9',03) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0120 := '+' + StrTran(Transform((cTrab)->POS0120,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0141 := '+' + StrTran(Transform((cTrab)->POS0141,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0162 := '+' + StrTran(Transform((cTrab)->POS0162,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0183 := '+' + StrTran(Transform((cTrab)->POS0183,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0204 := '+' + StrTran(Transform((cTrab)->POS0204,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0225 := '+' + StrTran(Transform((cTrab)->POS0225,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0246 := '+' + StrTran(Transform((cTrab)->POS0246,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0267 := '+' + StrTran(Transform((cTrab)->POS0267,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0288 := '+' + StrTran(Transform((cTrab)->POS0288,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0309 := '+' + StrTran(Transform((cTrab)->POS0309,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0330 := '+' + StrTran(Transform((cTrab)->POS0330,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0351 := '+' + StrTran(Transform((cTrab)->POS0351,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0372 := '+' + StrTran(Transform((cTrab)->POS0372,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				
				MsUnLock()
				
				(cTrab)->(DbSkip())
				
				nCount++
				
			End Do
			
			// --------------------------------------------------------------
			
			U_SYNCDAT(cTempTrab,aSyncEnv,nCount)
			
			(cTrab)->(DbCloseArea())
			(cTempTrab)->(DbCloseArea())
			fErase(Separa(cTempFile,'\',.T.)[3])
			
		End IF
		
	Else
		
		U_SYNCLOG(;
			'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
			'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
			'Nao foi possivel montar o ambiente de geracao do layout ' + cLayout)
		
	End IF
	
	RpcClearEnv()
	
Return
